/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package controllers.utils;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.enterprise.context.SessionScoped;
import javax.inject.Named;
import models.Course;
import models.Department;
import models.Institute;
import models.StudyProgram;
/**
 *
 * @author dybvik
 */
@SessionScoped
@Named(value="allCourses")
public class allCourses implements Serializable {
    private String studprog;
    private String dep;
    private String inst;
    private final List<Course> courses = new ArrayList<Course>();
    private final List<StudyProgram> studyprograms = new ArrayList<StudyProgram>();
    private final List<Department> departments = new ArrayList<Department>();
    private final List<Institute> institutes = new ArrayList<Institute>();
    private final DatabaseHjelper dbh = DatabaseHjelper.hent();
    
    public allCourses() {
       
    }

    public String navToAllCourses(){
        updateSelectOneMenus();
        updateCourses(studprog, dep, inst);
        return "allCourses";
    }

    private String makeSQLSentence(){
        String sql = "SELECT * FROM course";
        
        if(!studprog.equalsIgnoreCase("") || studprog!=null) sql+=" studyProgram="+studprog;
        if(!dep.equalsIgnoreCase("") || dep!=null) sql+=" department="+dep;
        if(!inst.equalsIgnoreCase("") || inst!=null) sql+=" institute="+inst;


        return null;
    }

    private void updateCourses(String studprog, String dep, String inst){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet res = null;
        try {

            stmt = conn.prepareStatement("SELECT * FROM course");
            res = stmt.executeQuery();
            synchronized (courses) {
                courses.clear();
                while (res.next()){
                    Course s = new Course(res.getInt("courseID"), res.getString("courseCode"), res.getString("courseName"));
                    //System.out.println(s.toString());
                    courses.add(s);
                }
            }
        }catch (SQLException e) {
            System.out.println("update allCourses: " + e.getMessage());
        } finally {
            DatabaseHjelper.lukk(res);
            DatabaseHjelper.lukk(stmt);
            DatabaseHjelper.lukk(conn);
        }
    }

    private void updateSelectOneMenus(){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet res = null;
        try {
            conn = dbh.finnForbindelse();
            stmt = conn.prepareStatement("SELECT * FROM institute");
            res = stmt.executeQuery();
            synchronized (institutes) {
                institutes.clear();
                while (res.next()){
                    Institute i = new Institute(res.getInt("instituteID"), res.getString("instituteName"), res.getString("instAddress"), res.getString("phoneNr"));
                    //System.out.println(s.toString());
                    institutes.add(i);
                }
            }
            stmt = conn.prepareStatement("SELECT * FROM department");
            res = stmt.executeQuery();
                synchronized (departments) {
                departments.clear();
                while (res.next()){
                    Department d = new Department(res.getInt("departmentID"), res.getString("departmentName"), res.getString("depAddress"), res.getString("phoneNr"));
                    //System.out.println(s.toString());
                    departments.add(d);
                }
            }
            stmt = conn.prepareStatement("SELECT * FROM studyProgram");
            res = stmt.executeQuery();
            synchronized (studyprograms) {
                studyprograms.clear();
                while (res.next()){
                    StudyProgram s = new StudyProgram(res.getInt("studyProgramID"), res.getInt("studyProgramCode"), res.getString("studyProgramName"),
                            res.getDate("approved"), res.getDate("lastRevised"), res.getString("approvedBy"));
                    s.setIsNKRFromInt(res.getInt("isNKR"));
                    studyprograms.add(s);
                }
            }
        }catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            DatabaseHjelper.lukk(res);
            DatabaseHjelper.lukk(stmt);
            DatabaseHjelper.lukk(conn);
        }
    }


    public String getDep() {
        return dep;
    }

    public void setDep(String dep) {
        this.dep = dep;
    }

    public String getInst() {
        return inst;
    }

    public void setInst(String inst) {
        this.inst = inst;
    }

    public String getStudprog() {
        return studprog;
    }

    public void setStudprog(String studprog) {
        this.studprog = studprog;
    }

    public List<Course> getCourses() {
        return courses;
    }

    public List<Institute> getInstitutes(){
        return institutes;
    }

    public List<StudyProgram> getStudyprograms(){
        return studyprograms;
    }

    public List<Department> getDepartments(){
        return departments;
    }

    public List<Course> getCoursesInStudyprogram() {
        return courses;
    }

    public void coursesInStudyprogram(String spName){
        //ArrayList<Course> course = new ArrayList<Course>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet res = null;
        try {
            conn = dbh.finnForbindelse();
            stmt = conn.prepareStatement("select co.Coursename from "
                    + "studyprogramcourse spc, course co, studyprogram sp "
                    + "where co.COURSEID=spc.COURSEID and "
                    + "sp.STUDYPROGRAMID=spc.STUDYPROGRAMID and "
                    + "sp.STUDYPROGRAMNAME=?;");
            stmt.setString(1, spName);
            res = stmt.executeQuery();
            courses.clear();
            while (res.next()) {
                for(Course c : courses) {
                    if(spName == null ? c.getStudyprogram().getName() == null : spName.equals(c.getStudyprogram().getName()))
                    {
                        courses.add(c);
                    }
                }
           }
        } catch (SQLException e) {
            System.out.println("coursesInStudyProgram: " + e.getMessage());
        } finally {
            DatabaseHjelper.lukk(res);
            DatabaseHjelper.lukk(stmt);
            DatabaseHjelper.lukk(conn);
        }

    }



        public ArrayList<Course> getCoursesInStudyprogram(String spName) {
        ArrayList<Course> course = new ArrayList<Course>();
        for(Course c : courses) {
            if(spName == null ? c.getStudyprogram().getName() == null : spName.equals(c.getStudyprogram().getName())) {
                course.add(c);
            }
        }
        return course;
    }
     
     public String courseList(){
         getCoursesInStudyprogram();
         return "allCourses";
     }
    
}


